package org.springframework.howelly.controllers;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFPicture;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.util.IOUtils;
import org.springframework.faces.model.OneSelectionTrackingListDataModel;
import org.springframework.howelly.model.Gasto;
import org.springframework.howelly.utils.Constants;
import org.springframework.stereotype.Controller;
import org.springframework.webflow.execution.RequestContext;

@Controller
public class ExcelController {

    public ExcelController() {
    }

    @SuppressWarnings("unchecked")
    public void postProcessXLS(OneSelectionTrackingListDataModel gastos, RequestContext context) throws Exception {
	HSSFWorkbook wb = new HSSFWorkbook();
	List<Gasto> finanzas = (ArrayList<Gasto>) gastos.getWrappedData();
	HttpServletResponse response = (HttpServletResponse) context.getExternalContext().getNativeResponse();
	response.addHeader("Content-Disposition", "attachment; filename=howelly.xls");
	response.setContentType("application/vnd.ms-excel; charset=cp1252");

	exportXLS(wb, finanzas);
	ServletOutputStream out = response.getOutputStream();
	wb.write(out);
	out.flush();
    }

    public void exportIvaReport(RequestContext context, OneSelectionTrackingListDataModel gastosFrontEnd)
	    throws Exception {
	HSSFWorkbook wb = new HSSFWorkbook();
	HttpServletResponse response = (HttpServletResponse) context.getExternalContext().getNativeResponse();
	response.addHeader("Content-Disposition", "attachment; filename=howelly.xls");
	response.setContentType("application/vnd.ms-excel; charset=cp1252");
	List<Gasto> gastos = (ArrayList<Gasto>) gastosFrontEnd.getWrappedData();
	exportIvaReport(wb, gastos);
	ServletOutputStream out = response.getOutputStream();
	wb.write(out);
	out.flush();
    }

    private void exportXLS(HSSFWorkbook wb, List<Gasto> finanzas) {

	HSSFSheet sheet = wb.createSheet();
	sheet.setColumnWidth(0, 6000); // adjust width of the first column
	sheet.setColumnWidth(1, 3000); // adjust width of the second column
	sheet.setColumnWidth(2, 4000); // adjust width of the third column
	sheet.setColumnWidth(3, 3000); // adjust width of the fourth column
	sheet.setColumnWidth(4, 3000); // adjust width of the fifth column
	sheet.setColumnWidth(5, 3000); // adjust width of the sixth column
	sheet.setColumnWidth(6, 6000); // adjust width of the seventh column
	sheet.setColumnWidth(7, 3000); // adjust width of the eighth column
	sheet.setColumnWidth(8, 5000); // adjust width of the ninth column

	HSSFRow header = sheet.createRow(0);
	createHeader(wb, header);

	double totalGastos = 0;
	double totalIngresos = 0;
	double totalGastosNetos = 0;
	double saldoParcial = 0;
	Collections.sort(finanzas, Gasto.ByDateDesc);
	for (int i = 0; i < finanzas.size(); i++) {
	    Gasto gasto = finanzas.get(i);
	    HSSFRow row = sheet.createRow(i + 1);
	    row.createCell(0).setCellValue(new HSSFRichTextString(gasto.getName()));
	    row.createCell(1).setCellValue(new HSSFRichTextString(gasto.getExpenseType()));
	    row.createCell(2).setCellValue(new HSSFRichTextString(gasto.getDiaEfectuadoString()));
	    row.createCell(9).setCellValue(new HSSFRichTextString(gasto.getUser().getName()));
	    if (gasto.getExpenseType().equals(Constants.GASTO)) {
		totalGastos += gasto.getPrice().doubleValue();
		saldoParcial -= gasto.getPrice().doubleValue();
		totalGastosNetos += gasto.getPriceNeto().doubleValue();
		row.createCell(3).setCellValue(-gasto.getPrice().doubleValue());
		row.createCell(4).setCellValue(-gasto.getPriceNeto().doubleValue());
		row.createCell(5).setCellValue(saldoParcial);
		row.createCell(6).setCellValue(new HSSFRichTextString(gasto.getCategoriaName()));
		row.createCell(7).setCellValue(gasto.getIva().doubleValue());
		row.createCell(8).setCellValue(new HSSFRichTextString(gasto.getFactura()));

	    } else {
		totalIngresos += gasto.getPrice().doubleValue();
		saldoParcial += gasto.getPrice().doubleValue();
		row.createCell(3).setCellValue(gasto.getPrice().doubleValue());
		row.createCell(5).setCellValue(saldoParcial);

	    }

	}

	createFooterValues(wb, finanzas, totalGastos, totalIngresos, totalGastosNetos);

    }

    private void createHeader(HSSFWorkbook wb, HSSFRow header) {
	HSSFCellStyle cellStyle = wb.createCellStyle();
	cellStyle.setFillForegroundColor(HSSFColor.BLUE_GREY.index);
	cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

	header.createCell(0).setCellValue(new HSSFRichTextString("Nombre"));
	header.createCell(1).setCellValue(new HSSFRichTextString("Tipo"));
	header.createCell(2).setCellValue(new HSSFRichTextString("Dia Efectuado"));
	header.createCell(3).setCellValue(new HSSFRichTextString("Precio"));
	header.createCell(4).setCellValue(new HSSFRichTextString("Precio Neto"));
	header.createCell(5).setCellValue(new HSSFRichTextString("Saldo Parcial"));
	header.createCell(6).setCellValue(new HSSFRichTextString("Categoria"));
	header.createCell(7).setCellValue(new HSSFRichTextString("Iva"));
	header.createCell(8).setCellValue(new HSSFRichTextString("Factura"));
	header.createCell(9).setCellValue(new HSSFRichTextString("Usuario"));
	for (int i = 0; i < header.getPhysicalNumberOfCells(); i++) {
	    HSSFCell cell = header.getCell(i);
	    cell.setCellStyle(cellStyle);
	}
    }

    private void createFooterValues(HSSFWorkbook wb, List<Gasto> finanzas, double totalGastos, double totalIngresos,
	    double totalGastosNetos) {

	HSSFCellStyle totalStyle = wb.createCellStyle();
	totalStyle.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index);
	totalStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

	HSSFRow firstFooterRow = wb.getSheetAt(0).createRow(finanzas.size() + 3);
	firstFooterRow.createCell(0).setCellValue(new HSSFRichTextString("Totales : "));
	firstFooterRow.getCell(0).setCellStyle(totalStyle);

	firstFooterRow.createCell(3).setCellFormula("SUM(D1:D" + (finanzas.size() + 1) + ")");
	firstFooterRow.createCell(4).setCellFormula("SUM(E1:E" + (finanzas.size() + 1) + ")");
	firstFooterRow.createCell(7).setCellFormula("SUM(H1:H" + (finanzas.size() + 1) + ")");
	for (int j = 1; j < 10; j++) {
	    HSSFCell cell = firstFooterRow.getCell(j);
	    if (cell == null)
		firstFooterRow.createCell(j).setCellValue(new HSSFRichTextString());
	    firstFooterRow.getCell(j).setCellStyle(totalStyle);
	}

	HSSFRow secondFooterRow = wb.getSheetAt(0).createRow(finanzas.size() + 7);
	secondFooterRow.createCell(0).setCellValue(new HSSFRichTextString("Total de los Ingresos : "));
	secondFooterRow.createCell(1).setCellValue(totalIngresos);
	HSSFRow thirdFooterRow = wb.getSheetAt(0).createRow(finanzas.size() + 8);
	thirdFooterRow.createCell(0).setCellValue(new HSSFRichTextString("Total de los Gastos : "));
	thirdFooterRow.createCell(1).setCellValue(totalGastos);

	HSSFRow fourthFooterRow = wb.getSheetAt(0).createRow(finanzas.size() + 9);
	fourthFooterRow.createCell(0).setCellValue(new HSSFRichTextString("Total de los Gastos Netos : "));
	fourthFooterRow.createCell(1).setCellValue(totalGastosNetos);

    }

    private void exportIvaReport(HSSFWorkbook wb, List<Gasto> finanzas) throws IOException {

	HSSFSheet sheet = wb.createSheet();
	sheet.setColumnWidth(0, 6000); // adjust width of the first column
	sheet.setColumnWidth(1, 3000); // adjust width of the second column
	sheet.setColumnWidth(2, 4000); // adjust width of the third column
	sheet.setColumnWidth(3, 3000); // adjust width of the fourth column
	sheet.setColumnWidth(4, 3000); // adjust width of the fifth column
	sheet.setColumnWidth(5, 3000); // adjust width of the sixth column
	sheet.setColumnWidth(6, 6000); // adjust width of the seventh column
	sheet.setColumnWidth(7, 3000); // adjust width of the eighth column
	sheet.setColumnWidth(8, 5000); // adjust width of the ninth column
	sheet.setColumnWidth(9, 5000); // adjust width of the ninth column

	String currentdir = System.getProperty("user.dir");
	System.out.println("current DIR" + currentdir);
	File file = new File("logo.jpeg");
	FileInputStream fis = null;
	try {
	    fis = new FileInputStream(file);

	    // Here BufferedInputStream is added for fast reading.
	    byte[] bytes = IOUtils.toByteArray(fis);
	    int pictureIdx = wb.addPicture(bytes, HSSFWorkbook.PICTURE_TYPE_JPEG);
	    HSSFClientAnchor anchor = new HSSFClientAnchor();
	    // Create the drawing patriarch. This is the top level container for all shapes.
	    HSSFPatriarch drawing = sheet.createDrawingPatriarch();
	    drawing.setCoordinates(pictureIdx, 0, 0, 0);
	    HSSFPicture pict = drawing.createPicture(anchor, pictureIdx);
	    pict.resize();

	} catch (FileNotFoundException e) {
	    e.printStackTrace();
	} catch (IOException e) {
	    e.printStackTrace();
	}
	// Header
	HSSFRow header = sheet.createRow(10);
	HSSFCellStyle cellStyle = wb.createCellStyle();
	cellStyle.setFillForegroundColor(HSSFColor.BLUE_GREY.index);
	cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

	header.createCell(0).setCellValue(new HSSFRichTextString("Nombre"));
	header.createCell(1).setCellValue(new HSSFRichTextString("Tipo"));
	header.createCell(2).setCellValue(new HSSFRichTextString("Dia Efectuado"));
	header.createCell(3).setCellValue(new HSSFRichTextString("Precio"));
	header.createCell(4).setCellValue(new HSSFRichTextString("Precio Neto"));
	header.createCell(5).setCellValue(new HSSFRichTextString("Saldo Parcial"));
	header.createCell(6).setCellValue(new HSSFRichTextString("Categoria"));
	header.createCell(7).setCellValue(new HSSFRichTextString("Iva"));
	header.createCell(8).setCellValue(new HSSFRichTextString("Factura"));
	header.createCell(9).setCellValue(new HSSFRichTextString("CUIT"));
	for (int i = 0; i < header.getPhysicalNumberOfCells(); i++) {
	    HSSFCell cell = header.getCell(i);
	    cell.setCellStyle(cellStyle);
	}

	// Content
	double totalGastos = 0;
	double totalIngresos = 0;
	double totalGastosNetos = 0;
	double saldoParcial = 0;
	Collections.sort(finanzas, Gasto.ByDateDesc);
	for (int i = 10; i < finanzas.size(); i++) {
	    Gasto gasto = finanzas.get(i);
	    HSSFRow row = sheet.createRow(i + 1);
	    row.createCell(0).setCellValue(new HSSFRichTextString(gasto.getName()));
	    row.createCell(1).setCellValue(new HSSFRichTextString(gasto.getExpenseType()));
	    row.createCell(2).setCellValue(new HSSFRichTextString(gasto.getDiaEfectuadoString()));
	    row.createCell(3).setCellValue(-gasto.getPrice().doubleValue());
	    row.createCell(4).setCellValue(-gasto.getPriceNeto().doubleValue());
	    row.createCell(5).setCellValue(saldoParcial);
	    row.createCell(6).setCellValue(new HSSFRichTextString(gasto.getCategoriaName()));
	    row.createCell(7).setCellValue(gasto.getIva().doubleValue());
	    row.createCell(8).setCellValue(new HSSFRichTextString(gasto.getFactura()));
	    row.createCell(9).setCellValue(new HSSFRichTextString(gasto.getCuit()));
	    totalGastos += gasto.getPrice().doubleValue();
	    saldoParcial -= gasto.getPrice().doubleValue();
	    totalGastosNetos += gasto.getPriceNeto().doubleValue();

	}

	createFooterValues(wb, finanzas, totalGastos, totalIngresos, totalGastosNetos);

    }

}
